﻿using System;
using System.Collections.Concurrent;
using System.Data;
using System.Data.Common;
using System.Reflection;

namespace CC.Core
{
    public class DBHelper
    {
        private readonly static DataSource dataSource;
 
        static DBHelper()
        {
            dataSource = new DataSource("mysql_test", "mysql", "Data Source=124.222.106.239;Database=ldh;User Id=root;Password=1qazxdr5432;Charset=utf8;", "MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data");
            RegisterDbProvider();
        }

        #region 设置数据提供者、数据源等配置

        /// <summary>
        /// 注册数据库工厂，用以支持对应的数据库类型
        /// </summary>
        /// <param name="providerName">名称，唯一</param>
        /// <param name="providerFactoryClassName">数据库工厂对应的类全名称，如：MySql.Data.MySqlClient.MySqlClientFactory</param>
        public static void RegisterDbProvider()
        {
            DbProviderFactories.RegisterFactory(dataSource.ProviderName, dataSource.ProviderFactoryClassName);
        }

        /// <summary>
        /// 取消注册的数据库工厂
        /// </summary>
        /// <param name="providerName">名称，唯一</param>
        public static bool UnregisterDbProvider(string providerName)
        {
            return DbProviderFactories.UnregisterFactory(providerName);
        }

        /// <summary>
        /// 数据提供者工厂
        /// </summary>
        /// <param name="datasourceName">数据源名称</param>
        /// <returns></returns>
        public static DbProviderFactory GetDbProviderFactory()
        {
            return DbProviderFactories.GetFactory(dataSource.ProviderName);
        }

        #endregion 设置数据提供者、数据源等配置

        #region 创建各种数据对象

        /// <summary>
        /// 默认执行命令超时时间，单位：秒
        /// </summary>
        public static int DefaultCommandTimeout { get; set; } = 60;

        /// <summary>
        /// 数据库连接对象
        /// </summary>
        /// <returns></returns>
        public static DbConnection GetConnection()
        {
            DbProviderFactory factory = GetDbProviderFactory();
            DbConnection connection = factory.CreateConnection()!;
            connection.ConnectionString = dataSource.ConnectionString;
            return connection;
        }

        /// <summary>
        /// 创建数据库命令对象
        /// </summary>
        /// <returns></returns>

        public static DbCommand CreateCommand(int timeout)
        {
            DbProviderFactory factory = GetDbProviderFactory();
            DbCommand command = factory.CreateCommand()!;
            command.CommandTimeout = timeout;
            return command;
        }

        /// <summary>
        /// 创建数据库命令对象
        /// </summary>
        /// <param name="connection"></param>
        /// <returns></returns>
        public static DbCommand CreateCommand(DbConnection connection)
        {
            return CreateCommand(connection, DefaultCommandTimeout);
        }

        public static DbCommand CreateCommand(DbConnection connection, int timeout)
        {
            DbCommand command = connection.CreateCommand();
            command.CommandTimeout = timeout;
            return command;
        }

        /// <summary>
        /// 创建数据源对应的命令变量
        /// </summary>
        /// <param name="name">变量名</param>
        /// <param name="value">变量值</param>
        /// <returns></returns>
        public static DbParameter CreateParam(string name, object value)
        {
            DbProviderFactory factory = GetDbProviderFactory();
            DbParameter parameter = factory.CreateParameter()!;
            parameter.ParameterName = name;
            parameter.Value = value;
            return parameter;
        }

        public static DbParameter CreateParam(string name, object value, DbType type, ParameterDirection dir)
        {
            DbParameter parameter = CreateParam(name, value);
            parameter.DbType = type;
            parameter.Direction = dir;
            return parameter;
        }

        #endregion 创建各种数据对象

        #region 事务相关

        public static DbTransaction GetTransaction(IsolationLevel level)
        {
            DbConnection connection = GetConnection();
            connection.Open();
            return connection.BeginTransaction(level);
        }

        public static void CommitTransaction(DbTransaction transaction)
        {
            DbConnection connection = transaction.Connection!;
            transaction.Commit();
            connection.Close();
        }

        public static void RollbackTransaction(DbTransaction transaction)
        {
            DbConnection connection = transaction.Connection!;
            transaction.Rollback();
            connection.Close();
        }

        #endregion 事务相关

        #region 参数准备

        /// <summary>
        /// 为DbCommand对象设置参数信息
        /// </summary>
        /// <param name="command">DbCommand对象</param>
        /// <param name="transaction">事务对象</param>
        /// <param name="commandType">Command类型</param>
        /// <param name="commandText">Command命令字符串</param>
        /// <param name="commandParams">参数数组</param>
        /// <param name="needCloseConnection">是否需要关闭DbConnection，用于返回</param>
        private static void SetCommandParams(DbCommand command, DbTransaction transaction, CommandType commandType,
            string commandText, DbParameter[] commandParams, out bool needCloseConnection)
        {
            if (command == null)
            {
                throw new Exception("无效的command");
            }
            if (command.Connection!.State != ConnectionState.Open)
            {
                command.Connection.Open();
                needCloseConnection = true;
            }
            else
            {
                needCloseConnection = false;
            }
            if (transaction != null)
            {
                command.Transaction = transaction;
            }
            command.CommandType = commandType;
            command.CommandText = commandText;
            if (commandParams != null)
            {
                AttachParameters(command, commandParams);
            }
        }

        /// <summary>
        /// 为command对象添加参数
        /// </summary>
        /// <param name="command"></param>
        /// <param name="commandParams"></param>
        private static void AttachParameters(DbCommand command, DbParameter[] commandParams)
        {
            if (commandParams != null)
            {
                foreach (DbParameter param in commandParams)
                {
                    if (param != null)
                    {
                        if ((param.Direction == ParameterDirection.Input ||
                            param.Direction == ParameterDirection.InputOutput) &&
                            param.Value == null)
                        {
                            param.Value = DBNull.Value;
                        }
                        command.Parameters.Add(param);
                    }
                }
            }
        }

        #endregion 参数准备

        #region ExecuteNonQuery

        public static int ExecuteNonQuery(string commandText)
        {
            using (DbConnection connection = GetConnection())
            {
                return ExecuteNonQuery(connection, commandText);
            }
        }

        public static int ExecuteNonQuery(CommandType commandType, string commandText, params DbParameter[] commandParams)
        {
            using (DbConnection connection = GetConnection())
            {
                return ExecuteNonQuery(connection, commandType, commandText, commandParams);
            }
        }

        public static int ExecuteNonQuery(DbConnection connection, string commandText)
        {
            return ExecuteNonQuery(connection, commandText, null);
        }

        public static int ExecuteNonQuery(DbConnection connection, string commandText, params DbParameter[] commandParams)
        {
            return ExecuteNonQuery(connection, CommandType.Text, commandText, commandParams);
        }

        /// <summary>
        /// 无返回查询
        /// </summary>
        /// <param name="connection">数据连接对象</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令字符串</param>
        /// <param name="commandParams">命令参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(DbConnection connection, CommandType commandType, string commandText, params DbParameter[] commandParams)
        {
            int result = 0;
            DbCommand command = CreateCommand(connection);
            bool needCloseConnection = false;
            SetCommandParams(command, null, commandType, commandText, commandParams, out needCloseConnection);
            try
            {
                result = command.ExecuteNonQuery();
                if (result < 0)
                {
                    result = 0;
                }
            }
            finally
            {
                command.Parameters.Clear();
                if (needCloseConnection)
                {
                    connection.Close();
                }
            }
            return result;
        }

        public static int ExecuteNonQuery(DbTransaction transaction, string commandText)
        {
            return ExecuteNonQuery(transaction, commandText, null);
        }

        public static int ExecuteNonQuery(DbTransaction transaction, string commandText, params DbParameter[] commandParams)
        {
            return ExecuteNonQuery(transaction, CommandType.Text, commandText, commandParams);
        }

        /// <summary>
        /// 无返回查询
        /// </summary>
        /// <param name="transaction">数据库事务对象</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令字符串</param>
        /// <param name="commandParams">命令参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(DbTransaction transaction, CommandType commandType, string commandText, params DbParameter[] commandParams)
        {
            if (transaction == null)
            {
                throw new Exception("无效的Transaction");
            }
            if (transaction.Connection == null)
            {
                throw new Exception("当前Transaction已经回滚或提交，请提供一个有效的Transaction");
            }
            DbCommand command = CreateCommand(transaction.Connection);
            bool needCloseConnection = false;

            SetCommandParams(command, transaction, commandType, commandText, commandParams, out needCloseConnection);
            int result = 0;

            try
            {
                result = command.ExecuteNonQuery();
                if (result < 0)
                {
                    result = 0;
                }
            }
            finally
            {
                command.Parameters.Clear();
                if (needCloseConnection)
                {
                    transaction.Connection.Close();
                }
            }

            return result;
        }

        #endregion ExecuteNonQuery

        #region ExecuteScalar

        public static object ExecuteScalar(string commandText)
        {
            using (DbConnection connection = GetConnection())
            {
                return ExecuteScalar(connection, commandText);
            }
        }

        public static object ExecuteScalar(string commandText, params DbParameter[] commandParams)
        {
            return ExecuteScalar(CommandType.Text, commandText, commandParams);
        }

        public static object ExecuteScalar(CommandType commandType, string commandText, params DbParameter[] commandParams)
        {
            using (DbConnection connection = GetConnection())
            {
                return ExecuteScalar(connection, commandType, commandText, commandParams);
            }
        }

        public static object ExecuteScalar(DbConnection connection, string commandText)
        {
            return ExecuteScalar(connection, commandText, null);
        }

        public static object ExecuteScalar(DbConnection connection, string commandText, params DbParameter[] commandParams)
        {
            return ExecuteScalar(connection, CommandType.TableDirect, commandText, commandParams);
        }

        /// <summary>
        /// 返回查询结果的第一行的第一列的内容
        /// </summary>
        /// <param name="connection">数据连接对象</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令字符串</param>
        /// <param name="commandParams">命令参数</param>
        /// <returns></returns>
        public static object ExecuteScalar(DbConnection connection, CommandType commandType, string commandText, params DbParameter[] commandParams)
        {
            object result = null;
            DbCommand command = CreateCommand(connection);
            bool needCloseConnection = false;
            SetCommandParams(command, null, commandType, commandText, commandParams, out needCloseConnection);
            try
            {
                result = command.ExecuteScalar()!;
            }
            finally
            {
                command.Parameters.Clear();
                if (needCloseConnection)
                {
                    connection.Close();
                }
            }
            return result;
        }

        public static object ExecuteScalar(DbTransaction transaction, string commandText)
        {
            return ExecuteScalar(transaction, commandText, null);
        }

        public static object ExecuteScalar(DbTransaction transaction, string commandText, params DbParameter[] commandParams)
        {
            return ExecuteScalar(transaction, CommandType.Text, commandText, commandParams);
        }

        /// <summary>
        /// 返回查询结果的第一行的第一列的内容
        /// </summary>
        /// <param name="transaction">数据库事务对象</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令字符串</param>
        /// <param name="commandParams">命令参数</param>
        /// <returns></returns>
        public static object ExecuteScalar(DbTransaction transaction, CommandType commandType, string commandText, params DbParameter[] commandParams)
        {
            if (transaction == null)
            {
                throw new Exception("无效的Transaction");
            }
            if (transaction.Connection == null)
            {
                throw new Exception("当前Transaction已经回滚或提交，请提供一个有效的Transaction");
            }
            DbCommand command = CreateCommand(transaction.Connection);
            bool needCloseConnection = false;

            SetCommandParams(command, transaction, commandType, commandText, commandParams, out needCloseConnection);

            object result = null;
            try
            {
                result = command.ExecuteScalar()!;
            }
            finally
            {
                command.Parameters.Clear();
                if (needCloseConnection)
                {
                    transaction.Connection.Close();
                }
            }
            return result;
        }

        #endregion ExecuteScalar

        #region ExecuteReader

        public static DbDataReader ExecuteDataReader(string commandText)
        {
            using (DbConnection connection = GetConnection())
            {
                return ExecuteDataReader(connection, commandText);
            }
        }

        public static DbDataReader ExecuteDataReader(string commandText, params DbParameter[] commandParams)
        {
            return ExecuteDataReader(CommandType.Text, commandText, commandParams);
        }

        public static DbDataReader ExecuteDataReader(CommandType commandType, string commandText, params DbParameter[] commandParams)
        {
            DbConnection connection = GetConnection();
            return ExecuteDataReader(connection, commandType, commandText, commandParams);
        }

        public static DbDataReader ExecuteDataReader(DbConnection connection, string commandText)
        {
            return ExecuteDataReader(connection, commandText, null);
        }

        public static DbDataReader ExecuteDataReader(DbConnection connection, string commandText, params DbParameter[] commandParams)
        {
            return ExecuteDataReader(connection, CommandType.Text, commandText, commandParams);
        }

        /// <summary>
        /// 返回查询结果集
        /// </summary>
        /// <param name="connection">数据连接对象</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令字符串</param>
        /// <param name="commandParams">命令参数</param>
        /// <returns></returns>
        public static DbDataReader ExecuteDataReader(DbConnection connection, CommandType commandType, string commandText, params DbParameter[] commandParams)
        {
            DbCommand command = CreateCommand(connection);
            bool needCloseConnection = false;
            SetCommandParams(command, null, commandType, commandText, commandParams, out needCloseConnection);

            DbDataReader result = null;
            try
            {
                if (needCloseConnection)
                {
                    result = command.ExecuteReader(CommandBehavior.CloseConnection);
                }
                else
                {
                    result = command.ExecuteReader();
                }
            }
            catch
            {
                if (needCloseConnection)
                {
                    connection.Close();
                }
                throw;
            }
            finally
            {
                command.Parameters.Clear();
            }
            return result;
        }

        public static DbDataReader ExecuteDataReader(DbTransaction transaction, string commandText)
        {
            return ExecuteDataReader(transaction, commandText, null);
        }

        public static DbDataReader ExecuteDataReader(DbTransaction transaction, string commandText, params DbParameter[] commandParams)
        {
            return ExecuteDataReader(transaction, CommandType.Text, commandText, commandParams);
        }

        /// <summary>
        /// 返回查询结果集
        /// </summary>
        /// <param name="transaction">数据库事务对象</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">命令字符串</param>
        /// <param name="commandParams">命令参数</param>
        /// <returns></returns>
        public static DbDataReader ExecuteDataReader(DbTransaction transaction, CommandType commandType, string commandText, params DbParameter[] commandParams)
        {
            if (transaction == null)
            {
                throw new Exception("无效的Transaction");
            }
            if (transaction.Connection == null)
            {
                throw new Exception("当前Transaction已经回滚或提交，请提供一个有效的Transaction");
            }
            DbCommand command = CreateCommand(transaction.Connection);
            bool needCloseConnection = false;

            SetCommandParams(command, transaction, commandType, commandText, commandParams, out needCloseConnection);

            DbDataReader result = null;
            try
            {
                if (needCloseConnection)
                {
                    result = command.ExecuteReader(CommandBehavior.CloseConnection);
                }
                else
                {
                    result = command.ExecuteReader();
                }
            }
            catch
            {
                if (needCloseConnection)
                {
                    transaction.Connection.Close();
                }
                throw;
            }
            finally
            {
                command.Parameters.Clear();
            }
            return result;
        }

        #endregion ExecuteReader

        #region List Search
        /// <summary>
        /// 查询返回对象列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">sql语句</param>
        /// <param name="func">dr对象处理</param>
        /// <param name="commandParameters">可选参数</param>
        /// <returns></returns>
        public static List<T> Query<T>(string commandText)
        { 
            return Query<T>(CommandType.Text, commandText, null);
        }

        /// <summary>
        /// 查询返回对象列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">sql语句</param>
        /// <param name="func">dr对象处理</param>
        /// <param name="commandParameters">可选参数</param>
        /// <returns></returns>
        public static List<T> Query<T>(CommandType commandType, string commandText)
        {
            return Query<T>(commandType, commandText, null);
        }

        /// <summary>
        /// 查询返回对象列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="commandType">命令类型</param>
        /// <param name="commandText">sql语句</param>
        /// <param name="func">dr对象处理</param>
        /// <param name="commandParameters">可选参数</param>
        /// <returns></returns>
        public static List<T> Query<T>(CommandType commandType, string commandText, params DbParameter[] commandParams)
        {
            var lst = new List<T>();
            DbConnection connection = GetConnection();
            try
            {
                using (var dr = ExecuteDataReader(connection, commandType, commandText, commandParams))
                {
                    while (dr.Read())
                    {
                        lst.Add(GetModel<T>(dr));
                    }
                }
            }
            catch
            {
                if (connection != null) connection.Close();
                throw;
            }
            return lst;
        }

        private static T GetModel<T>(IDataReader reader)
        {
            //获取字段总数
            int colCount = reader.FieldCount;
            //获取实体类型
            Type objType = typeof(T);
            //实例化实体
            ConstructorInfo constructor = objType.GetConstructor(Type.EmptyTypes)!;
            T entity = (T)constructor.Invoke(null);
            //给实体赋值
            for (int i = 0; i < colCount; i++)
            {
                PropertyInfo property = objType.GetProperty(reader.GetName(i))!;
                if (property != null)
                {
                    property.SetValue(entity, reader[i], null);
                }
            }
            return entity;
        }

        #endregion List begin
    }
}